use std::fmt::Display;

use chrono::{DateTime, TimeZone, Utc};
use itertools::Itertools;
use prost_types::Timestamp;
use tracing::info;

use crate::{
    pb::{IdQuery, QueryRequest, QueryRequestBuilder, RawQueryRequest, TimeQuery, User},
    ResponseStream, UserStatsService,
};

impl UserStatsService {
    pub async fn query(&self, req: QueryRequest) -> ResponseStream {
        let mut sql = "SELECT email,name FROM user_stats WHERE ".to_string();
        let timestamps_cond = req
            .timestamps
            .into_iter()
            .map(|(k, v)| timestamp_query(&k, v.lower, v.upper))
            .join(" AND ");
        sql.push_str(&timestamps_cond);
        let id_cond = req
            .ids
            .into_iter()
            .map(|(k, v)| ids_query(&k, &v.ids))
            .join(" AND ");
        sql.push_str(" AND ");
        sql.push_str(&id_cond);
        self.raw_query(RawQueryRequest { query: sql }).await
    }
    pub async fn raw_query(&self, req: RawQueryRequest) -> ResponseStream {
        let Ok(ret) = sqlx::query_as::<_, User>(&req.query)
            .fetch_all(&self.pool)
            .await
        else {
            return Box::pin(futures::stream::once(async move {
                Err(tonic::Status::internal(format!(
                    "Failed to fetch data with query: {}",
                    req.query
                )))
            }));
        };
        Box::pin(futures::stream::iter(ret.into_iter().map(Ok)))
    }
}

fn ids_query(name: &str, ids: &[u64]) -> String {
    if ids.is_empty() {
        return "TRUE".to_string();
    }
    format!("array{:?} <@ {} ", ids, name)
}
fn timestamp_query(name: &str, lower: Option<Timestamp>, upper: Option<Timestamp>) -> String {
    if lower.is_none() && upper.is_none() {
        return "TRUE".to_string();
    }
    if lower.is_none() {
        return format!("{} <= {}", name, ts_to_utc(upper.unwrap()).to_rfc3339());
    }
    if upper.is_none() {
        return format!("{} >= {}", name, ts_to_utc(lower.unwrap()).to_rfc3339());
    }
    format!(
        "{} >= {} and {} <= {}",
        name,
        ts_to_utc(lower.unwrap()).to_rfc3339(),
        name,
        ts_to_utc(upper.unwrap()).to_rfc3339()
    )
}

fn ts_to_utc(ts: Timestamp) -> DateTime<Utc> {
    Utc.timestamp_opt(ts.seconds, ts.nanos as _).unwrap()
}

impl Display for QueryRequest {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        let mut sql = "SELECT email,name FROM user_stats WHERE ".to_string();
        let timestamps_cond = self
            .timestamps
            .iter()
            .map(|(k, v)| timestamp_query(k, v.lower, v.upper))
            .join(" AND ");
        sql.push_str(&timestamps_cond);
        let id_cond = self
            .ids
            .iter()
            .map(|(k, v)| ids_query(k, &v.ids))
            .join(" AND ");
        if !id_cond.is_empty() {
            sql.push_str(" AND ");
            sql.push_str(&id_cond);
        }
        info!("Generated sql: {}", sql);
        write!(f, "{}", sql)
    }
}

impl QueryRequest {
    pub fn new_with_dt(name: &str, lower: DateTime<Utc>, upper: DateTime<Utc>) -> Self {
        let ts_lower = Timestamp {
            seconds: lower.timestamp(),
            nanos: 0,
        };
        let ts_upper = Timestamp {
            seconds: upper.timestamp(),
            nanos: 0,
        };
        let tq = TimeQuery {
            lower: Some(ts_lower),
            upper: Some(ts_upper),
        };
        QueryRequestBuilder::default()
            .timestamp((name.to_string(), tq))
            .build()
            .expect("build query request failed")
    }
    pub fn new_with_dt_and_ids(
        name: &str,
        lower: DateTime<Utc>,
        upper: DateTime<Utc>,
        id_name: &str,
        ids: &[u64],
    ) -> Self {
        let ts_lower = Timestamp {
            seconds: lower.timestamp(),
            nanos: 0,
        };
        let ts_upper = Timestamp {
            seconds: upper.timestamp(),
            nanos: 0,
        };
        let tq = TimeQuery {
            lower: Some(ts_lower),
            upper: Some(ts_upper),
        };
        let idq = IdQuery { ids: ids.to_vec() };
        QueryRequestBuilder::default()
            .timestamp((name.to_string(), tq))
            .id((id_name.to_string(), idq))
            .build()
            .expect("build query request failed")
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use chrono::{TimeZone, Utc};

    #[test]
    fn query_request_to_string_should_work() {
        let d1 = Utc.with_ymd_and_hms(2024, 1, 1, 0, 0, 0).unwrap();
        let d2 = d1 + chrono::Duration::days(1);
        let req = QueryRequest::new_with_dt("created_at", d1, d2);
        println!("req: {}", req);
        assert_eq!(
            req.to_string(),
            "SELECT email,name FROM user_stats WHERE created_at >= 2024-01-01T00:00:00+00:00 and created_at <= 2024-01-02T00:00:00+00:00"
        );
    }
}
